import datetime

import pandas as pd
from sklearn.preprocessing import OneHotEncoder
import os
from src.mobile.model.mobile_model_config import MODEL_DAYS, EVN_TEMPLATE_ID
from src.mobile.preprocessor import prop_preprocessor as prop
from src.mobile.preprocessor.model_data_preprocessor import preprocess_model_data,preprocess_rare_product_model_data_oversampling
from src.utils.config import logger
from src.utils.db_processor import presto_processor
from src.utils.util import get_today, check_date_str, format_date_string,check_conflict_file,check_dir
from src.mobile.model import mobile_model_config
from src.mobile.anomaly.anomaly_load import load_anomaly_document_item_exclude
import numpy as np
import pickle


SETTLE_DATA = """
(
SELECT distinct
cdp.document_item_id,
psdp.settle_amount_num AS item_quotation_price_num,

case when  psdp.settle_document_create_date<=date'2023-01-17' then cast(psdp.settle_document_create_date+interval'9' day as date) 
else cast(psdp.settle_document_create_date as date) end settle_list_create_date,
cdp.create_date AS shop_out_date,
dp.product_id,
dp.product_name,
CASE WHEN otpm.propertyleveltemplateid IS NULL THEN -1 ELSE otpm.propertyleveltemplateid END AS product_level_template_id,

dps.product_sku_id AS product_sku_key,
pssp.property_name AS price_property_name_name,
pssp.property_value_name AS price_property_value_name,
dp.product_brand_name,
case when tl.new_level_id>0 then tl.old_level_name else level.product_level_name end  AS product_level_name,
cdp.product_no
FROM dw.dw_centre_document_product cdp
JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
JOIN dw.dw_platform_document_product pdp ON pdp.product_no = psdp.product_no AND pdp.quotation_document_no = psdp.quotation_document_no
JOIN dim.dim_product_sku_channel_mapping dps ON dps.product_sku_id = cdp.product_combine_sku_id and dps.business_channel_id=1
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
 JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpmm ON dp.product_id = otpmm.product_id
JOIN ods.ods_trunk_producttemplatemapping otpm ON dp.product_id = otpm.productid AND otpm.templatetype = 1 and otpm.business_channel = '0'

JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = dps.product_sku_id
join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level
on pdp.product_no =sku2level.product_no 
--and sku2level.partition_date = date_format(current_date-interval '2' day,'%Y-%m-%d')
 left join algo.algo_c2b_sku3_to_sku2_mapping tl on 
tl.new_level_id=sku2level.secondary_level_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date) 
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286)
AND pdp.quotation_document_type_id = 10
AND cdp.create_date >=cast('{}' as date) 
and cdp.create_date not between  date'2023-01-17' and date'2023-01-25'
and psdp.settle_status_id not in (-1,5,9)
and psdp.settle_amount_num<=15000
and psdp.start_price>0
and ((abs(psdp.settle_amount_num-psdp.start_price)/psdp.start_price<=1 AND psdp.start_price>100) 
OR (abs(psdp.settle_amount_num-psdp.start_price)/psdp.start_price<=2 AND psdp.start_price<=100))
and sku2level.secondary_level_id>0
and pdp.product_source_id in (101,103)
and level.product_level_name is not null
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
)
union 
(
SELECT 
distinct
cdp.document_item_id,
psdp.product_sale_amount AS item_quotation_price_num,

case when  psdp.settle_document_create_date<=date'2023-01-17' then cast(psdp.settle_document_create_date+interval'9' day as date) 
else cast(psdp.settle_document_create_date as date) end settle_list_create_date,
cdp.create_date AS shop_out_date,
dp.product_id,
dp.product_name,
CASE WHEN otpm.propertyleveltemplateid IS NULL THEN -1 ELSE otpm.propertyleveltemplateid END AS product_level_template_id,

dps.product_sku_id AS product_sku_key,
pssp.property_name AS price_property_name_name,
pssp.property_value_name AS price_property_value_name,
dp.product_brand_name,
case when tl.new_level_id>0 then tl.old_level_name else level.product_level_name end  AS product_level_name,
cdp.product_no
FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
JOIN dim.dim_product_sku_channel_mapping dps ON dps.product_sku_id = cdp.product_combine_sku_id and dps.business_channel_id=1
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
 JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpmm ON dp.product_id = otpmm.product_id
JOIN ods.ods_trunk_producttemplatemapping otpm ON dp.product_id = otpm.productid AND otpm.templatetype = 1 and otpm.business_channel = '0'
JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = dps.product_sku_id
join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level
on cdp.product_no =sku2level.product_no 
 left join algo.algo_c2b_sku3_to_sku2_mapping tl on 
tl.new_level_id=sku2level.secondary_level_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
left join dw.dw_centre_document_product_out_info cdp_o on  cdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date) 
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 150  
and psdp.product_source_id in (101)
AND cdp.create_date >= cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and position('备件库' in psdp.product_combine_sku_name)=0
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)

)
"""
#and dp.product_id in  (32291,32292,36439,36291)

# virtual_data_sql="""
# SELECT distinct
# fsd.document_item_id,
# fsd.good_publish_price*fsd.to_s_rate_config*fsd.rate*0.9 as item_quotation_price_num,
# fsd.settle_list_create_date,
# fsd.shop_out_date,
# CASE WHEN otpm.propertyleveltemplateid IS NULL THEN -1 ELSE otpm.propertyleveltemplateid END AS product_level_template_id,
# CAST(fsd.product_name AS text) as product_name,
# fsd.product_id,
# fsd.product_brand_name,
# fsd.product_level_name,
# '23524525' as product_no,
# '23343' AS product_sku_key,
#
# case when fsd.brand_id=52 then 'unknown'  else fsd.property_value_back_name end as memory ,
# case when fsd.brand_id=52 then  fsd.property_value_back_name else 'unknown'end as storage,
#
# cpm.small_version ,
# '黑色' as color ,
# cpm.networks ,
# cpm.guarantee ,
# cpm.purchase_way
# FROM tmp.tmp_fictitious_settle_data_09_24 fsd
# inner join (select distinct product_id,brand_id,status
#     from tmp.tmp_model_product_property_max_price_config where status=1 and category_id=1
# ) product_config
# on fsd.product_id=product_config.product_id
# JOIN dm.dm_model_goods_code_property_mobile cpm  ON fsd.goods_code = cpm.goods_code
# JOIN ods.ods_trunk_producttemplatemapping otpm ON fsd.product_id = otpm.productid AND otpm.templatetype = 1 and otpm.business_channel = 0
#
# """
def load_settle_data(model_date=None):
    """
    读取结算数据
    :param model_date: 建模日期，如果为None，默认为当天
    :return:
    """
    if model_date is None:
        model_date = get_today()
    else:
        model_date = check_date_str(model_date)
    start_date = model_date - datetime.timedelta(days=MODEL_DAYS)
    d_start = datetime.datetime.strptime('2023-01-25', '%Y-%m-%d')
    if(start_date<d_start):
        diff=MODEL_DAYS+9
        start_date = model_date - datetime.timedelta(days=diff)
    logger.info('loading settle data from {}'.format(start_date))
    start_date = format_date_string(start_date)
    product_document_no_list = load_anomaly_document_item_exclude(start_date, 1)

    sql = SETTLE_DATA.format(start_date,start_date,start_date,start_date)
    data = presto_processor.load_sql(sql, parse_dates=['settle_list_create_date', 'shop_out_date'])
    data = data.loc[~(data.product_no.isin(product_document_no_list))]
    data.fillna(-1,inplace=True)
    ##存入pickle
    # gp_data = open('gp_data.pkl', 'wb')
    # pickle.dump(data, gp_data)
    # gp_data.close()
    # 读取pickle
    #gp_data = open('gp_data.pkl', 'rb')
    #data = pickle.load(gp_data)
    data['item_quotation_price_num'] = data['item_quotation_price_num'].astype(float)
    data['product_level_template_id'] = data['product_level_template_id'].astype(str)
    # 去掉环保机
    data = data[data['product_level_template_id'] != EVN_TEMPLATE_ID]
    # 将无属性-属性值的机器填充为unknown
    data = data.fillna('unknown')

    data['price_property_name_name'] = data['price_property_name_name'].map(lambda x: x.strip())
    data['price_property_value_name'] = data['price_property_value_name'].map(lambda x: x.strip())
    data['product_name'] = data['product_name'].map(lambda x: x.strip())
    data['product_brand_name'] = data['product_brand_name'].map(lambda x: x.strip())

    data = data[data['price_property_name_name'].isin(prop.PROP_LIST)]

    # 将长数据转为宽数据
    pivot_data = data.pivot_table(index=['document_item_id'],
                                  columns='price_property_name_name',
                                  values='price_property_value_name', aggfunc=lambda x: x.unique())


    pivot_data = pivot_data.fillna('unknown')
    pivot_data = pivot_data.reset_index()

    data = data.drop(columns=['price_property_name_name', 'price_property_value_name']).drop_duplicates()
    data = pd.merge(data, pivot_data, on='document_item_id')
    # virtual_df= postgre_processor.load_sql(virtual_data_sql)
    data = data.rename(columns=mobile_model_config.MOBILE_COLUMNS_MAPPING).copy()

    return data


def preprocess_settle_data(data=None, model_date=None):
    """
    预处理结算数据
    :param data: 结算原始数据，如果为None，那么先读取结算数据
    :param model_date: 建模日期
    :return:
    """
    if data is None:
        data = load_settle_data(model_date)

    # virtual_df.settle_list_create_date=virtual_df.settle_list_create_date.map(lambda x: np.datetime64(x))
    # virtual_df.shop_out_date=virtual_df.shop_out_date.map(lambda x: np.datetime64(x))
    #
    #
    # virtual_df.product_sku_key=virtual_df.product_sku_key.astype(np.int64)
    # data.product_level_template_id=data.product_level_template_id.astype(np.int64)
    # data = data.append(virtual_df)
    logger.info("the path:{}".format(os.getcwd()))
    check_dir(mobile_model_config.MODEL_DIR)
    model_file = os.path.join(mobile_model_config.MODEL_DIR, mobile_model_config.MODEL_DATA)
    flag=1
    if flag==1:
        check_conflict_file(mobile_model_config.MODEL_DIR,
                            mobile_model_config.MODEL_DATA,is_remove_old_file=True)
        ##存入pickle
        train_data = open(model_file, 'wb')
        pickle.dump(data, train_data)
        train_data.close()
    else:
        # 读取pickle
        train_data = open(model_file, 'rb')
        data = pickle.load(train_data)

    logger.info('loding data is end!')

    product_ohe = OneHotEncoder(handle_unknown='ignore')
    product_ohe.fit(data[['product_name']])

    data = preprocess_rare_product_model_data_oversampling(data)
    model_data = preprocess_model_data(data)
    logger.info("preprocess_settle_data is done!")
    return model_data, product_ohe
